
<!DOCTYPE html>
<html lang class="loading">
<head><meta name="generator" content="Hexo 3.9.0">
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
    <meta name="viewport" content="width=device-width, minimum-scale=1.0, maximum-scale=1.0, user-scalable=no">
    <title>MYSQL的主从同步，实现读写分离 - Hexo</title>
    <meta name="apple-mobile-web-app-capable" content="yes">
    <meta name="apple-mobile-web-app-status-bar-style" content="black-translucent">
    <meta name="google" content="notranslate">
    <meta name="keywords" content="Fechin,"> 
    <meta name="description" content="MySQL的主从复制，实现读写分离
在阿里云配置端口规则：3306
安装MySQL

在命令行工具输入：
12345678910111213wget &#39;https://dev.mysql.com/g,"> 
    <meta name="author" content="John Doe"> 
    <link rel="alternative" href="atom.xml" title="Hexo" type="application/atom+xml"> 
    <link rel="icon" href="/hexoboke/img/favicon.png"> 
    <link rel="stylesheet" href="//cdn.jsdelivr.net/npm/gitalk@1/dist/gitalk.css">
    <link rel="stylesheet" href="/hexoboke/css/diaspora.css">
    <script async src="//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js"></script>
    <script>
         (adsbygoogle = window.adsbygoogle || []).push({
              google_ad_client: "ca-pub-8691406134231910",
              enable_page_level_ads: true
         });
    </script>
    <script async custom-element="amp-auto-ads" src="https://cdn.ampproject.org/v0/amp-auto-ads-0.1.js">
    </script>
</head>
</html>
<body class="loading">
    <span id="config-title" style="display:none">Hexo</span>
    <div id="loader"></div>
    <div id="single">
    <div id="top" style="display: block;">
    <div class="bar" style="width: 0;"></div>
    <a class="icon-home image-icon" href="javascript:;" data-url="https://soulfeel.gitee.io"></a>
    <div title="播放/暂停" class="icon-play"></div>
    <h3 class="subtitle">MYSQL的主从同步，实现读写分离</h3>
    <div class="social">
        <!--<div class="like-icon">-->
            <!--<a href="javascript:;" class="likeThis active"><span class="icon-like"></span><span class="count">76</span></a>-->
        <!--</div>-->
        <div>
            <div class="share">
                <a title="获取二维码" class="icon-scan" href="javascript:;"></a>
            </div>
            <div id="qr"></div>
        </div>
    </div>
    <div class="scrollbar"></div>
</div>

    <div class="section">
        <div class="article">
    <div class='main'>
        <h1 class="title">MYSQL的主从同步，实现读写分离</h1>
        <div class="stuff">
            <span>六月 12, 2018</span>
            

        </div>
        <div class="content markdown">
            <h2 id="MySQL的主从复制，实现读写分离"><a href="#MySQL的主从复制，实现读写分离" class="headerlink" title="MySQL的主从复制，实现读写分离"></a>MySQL的主从复制，实现读写分离</h2><blockquote>
<p>在阿里云配置端口规则：<code>3306</code></p>
<h3 id="安装MySQL"><a href="#安装MySQL" class="headerlink" title="安装MySQL"></a>安装<code>MySQL</code></h3></blockquote>
<ul>
<li><p>在命令行工具输入：</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"></span><br><span class="line">wget <span class="string">'https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm'</span></span><br><span class="line"></span><br><span class="line">rpm -Uvh mysql57-community-release-el7<span class="number">-11.</span>noarch.rpm</span><br><span class="line"></span><br><span class="line">yum install -y mysql-community-server</span><br><span class="line"><span class="comment">#启动服务</span></span><br><span class="line">systemctl start mysqld</span><br><span class="line"><span class="comment">#查看服务状态</span></span><br><span class="line">systemctl status mysqld</span><br><span class="line"></span><br><span class="line">查看数据库密码</span><br><span class="line">grep <span class="string">'temporary password'</span> /var/log/mysqld.log</span><br></pre></td></tr></table></figure>

<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">然后利用这个密码进入mysql命令行 mysql -uroot -p查到的密码</span><br><span class="line"></span><br><span class="line">SET PASSWORD = PASSWORD(<span class="string">'Admin123!'</span>);</span><br><span class="line"></span><br><span class="line">设置远程登录</span><br><span class="line">GRANT ALL PRIVILEGES ON *.* TO <span class="string">'root'</span>@<span class="string">'%'</span> IDENTIFIED BY <span class="string">'Admin123!'</span> WITH GRANT OPTION;</span><br><span class="line">flush privileges;</span><br></pre></td></tr></table></figure>



</li>
</ul>
<ul>
<li><p>进入到<code>/etc/my.cnf</code>修改配置信息(<code>vim /etc/my.cnf</code>)</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">在[mysqld]下面添加，不需要分号</span><br><span class="line">字符集:注意是utf8而不是utf<span class="number">-8</span>!</span><br><span class="line">character-set-server=utf8</span><br><span class="line">这时候使用show variables like <span class="string">'char%'</span>;就可以查看到字符集都是utf8了</span><br><span class="line">sql支持group by语句</span><br><span class="line">sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION</span><br><span class="line">设置时区为东八区</span><br><span class="line">default-time_zone = <span class="string">'+8:00'</span></span><br></pre></td></tr></table></figure>
</li>
<li><p>重启服务</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">systemctl restart mysqld</span><br></pre></td></tr></table></figure>
</li>
<li><p>设置开机启动</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">systemctl enable mysqld</span><br><span class="line">systemctl daemon-reload</span><br></pre></td></tr></table></figure>
</li>
<li><p>测试MySQL服务</p>
</li>
</ul>
<blockquote>
<h3 id="主从同步"><a href="#主从同步" class="headerlink" title="主从同步"></a><code>主从同步</code></h3></blockquote>
<ul>
<li><p>首先准备两台阿里云服务器，一台作为主机（master），一台作为从机（slave），都安装好mysql5.7</p>
</li>
<li><p>进入master服务器</p>
</li>
<li><p>修改配置mysql配置文件 <code>vim /etc/my.cnf</code>，加入如下配置</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">server-id = <span class="number">1</span></span><br><span class="line">innodb_flush_log_at_trx_commit = <span class="number">2</span></span><br><span class="line">sync_binlog = <span class="number">1</span></span><br><span class="line">log-bin = mysql-bin<span class="number">-1</span></span><br></pre></td></tr></table></figure>
</li>
<li><p>保存后，重启myysql</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">systemctl restart mysql</span><br></pre></td></tr></table></figure>
</li>
<li><p>进入msql命令行<code>mysql -uroot -p你的密码</code></p>
</li>
<li><p>输入授权命令</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">GRANT REPLICATION SLAVE ON *.* to <span class="string">'repl'</span>@<span class="string">'%'</span> identified by <span class="string">'Admin123!'</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查看master的状态</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show master status;</span><br></pre></td></tr></table></figure>
</li>
<li><p>登录从机</p>
</li>
<li><p>修改slave服务器的MySQL配置<code>vim /etc/my.cnf</code> 加入下面的配置，需要注意的是server-id不要和master一样</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">server-id = <span class="number">201</span> </span><br><span class="line">innodb_flush_log_at_trx_commit = <span class="number">2</span> </span><br><span class="line">sync_binlog = <span class="number">1</span> </span><br><span class="line">log-bin = mysql-bin<span class="number">-201</span></span><br></pre></td></tr></table></figure>
</li>
<li><p>保存后重启服务</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">systemctl restart mysqld</span><br></pre></td></tr></table></figure>
</li>
<li><p>进入MySQL命令行，输入命令</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">change master to master_host = <span class="string">'39.106.228.179'</span>,master_user = <span class="string">'repl'</span> ,master_password = <span class="string">'Admin123!'</span>, master_log_file = <span class="string">'mysql-bin.000002'</span> ,master_log_pos = <span class="number">154</span></span><br></pre></td></tr></table></figure>

<ul>
<li><p>命令说明</p>
<blockquote>
<p>master_host：主机</p>
<p>master_user：主机授权用户</p>
<p>master_password：主机授权时候填写的密码</p>
<p>master_log_file：主机 show master status；中的File</p>
<p>master_log_pos：主机show master status；中的Position</p>
</blockquote>
</li>
</ul>
</li>
<li><p>输入命令启动slave</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">start slave;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查看slave的状态</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show slave status \G;</span><br></pre></td></tr></table></figure>

</li>
</ul>
<blockquote>
<p>mysql的读写分离主从配置并不是万能的，根据不停的应用场景选择不同的策略，mysql的主从复制功能有一定的延迟性，如果对数据实时一致性的要求比较高的场景不推荐使用。</p>
</blockquote>

            <!--[if lt IE 9]><script>document.createElement('audio');</script><![endif]-->
            <audio id="audio" loop="1" preload="auto" controls="controls" data-autoplay="false">
                <source type="audio/mpeg" src="">
            </audio>
            
                <ul id="audio-list" style="display:none">
                    
                        <li title='0' data-url='http://link.hhtjim.com/163/5146554.mp3'></li>
                    
                        <li title='1' data-url='http://link.hhtjim.com/qq/001faIUs4M2zna.mp3'></li>
                    
                </ul>
            
        </div>
        
    <div id='gitalk-container' class="comment link"
        data-ae='false'
        data-ci=''
        data-cs=''
        data-r=''
        data-o=''
        data-a=''
        data-d='false'
    >查看评论</div>


    </div>
    
</div>


    </div>
</div>
</body>
<script src="//cdn.jsdelivr.net/npm/gitalk@1/dist/gitalk.min.js"></script>
<script src="//lib.baomitu.com/jquery/1.8.3/jquery.min.js"></script>
<script src="/hexoboke/js/plugin.js"></script>
<script src="/hexoboke/js/diaspora.js"></script>
<link rel="stylesheet" href="/hexoboke/photoswipe/photoswipe.css">
<link rel="stylesheet" href="/hexoboke/photoswipe/default-skin/default-skin.css">
<script src="/hexoboke/photoswipe/photoswipe.min.js"></script>
<script src="/hexoboke/photoswipe/photoswipe-ui-default.min.js"></script>

<!-- Root element of PhotoSwipe. Must have class pswp. -->
<div class="pswp" tabindex="-1" role="dialog" aria-hidden="true">
    <!-- Background of PhotoSwipe. 
         It's a separate element as animating opacity is faster than rgba(). -->
    <div class="pswp__bg"></div>
    <!-- Slides wrapper with overflow:hidden. -->
    <div class="pswp__scroll-wrap">
        <!-- Container that holds slides. 
            PhotoSwipe keeps only 3 of them in the DOM to save memory.
            Don't modify these 3 pswp__item elements, data is added later on. -->
        <div class="pswp__container">
            <div class="pswp__item"></div>
            <div class="pswp__item"></div>
            <div class="pswp__item"></div>
        </div>
        <!-- Default (PhotoSwipeUI_Default) interface on top of sliding area. Can be changed. -->
        <div class="pswp__ui pswp__ui--hidden">
            <div class="pswp__top-bar">
                <!--  Controls are self-explanatory. Order can be changed. -->
                <div class="pswp__counter"></div>
                <button class="pswp__button pswp__button--close" title="Close (Esc)"></button>
                <button class="pswp__button pswp__button--share" title="Share"></button>
                <button class="pswp__button pswp__button--fs" title="Toggle fullscreen"></button>
                <button class="pswp__button pswp__button--zoom" title="Zoom in/out"></button>
                <!-- Preloader demo http://codepen.io/dimsemenov/pen/yyBWoR -->
                <!-- element will get class pswp__preloader--active when preloader is running -->
                <div class="pswp__preloader">
                    <div class="pswp__preloader__icn">
                      <div class="pswp__preloader__cut">
                        <div class="pswp__preloader__donut"></div>
                      </div>
                    </div>
                </div>
            </div>
            <div class="pswp__share-modal pswp__share-modal--hidden pswp__single-tap">
                <div class="pswp__share-tooltip"></div> 
            </div>
            <button class="pswp__button pswp__button--arrow--left" title="Previous (arrow left)">
            </button>
            <button class="pswp__button pswp__button--arrow--right" title="Next (arrow right)">
            </button>
            <div class="pswp__caption">
                <div class="pswp__caption__center"></div>
            </div>
        </div>
    </div>
</div>




</html>
